
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO







CREATE PROCEDURE [dbo].[usp_SearchLecture]
    (
      @JOL_Field_ApplyCompany VARCHAR(50),
      @JOL_Lecture_Title VARCHAR(50)
    )
AS 
    SELECT  a.JOL_Lecture_No,
            c.JOL_Employer_Name,
            a.JOL_Lecture_Title,
            b.JOL_Field_ApplyTime,
            b.JOL_Field_ApplyPosition,
            a.JOL_Lecture_Linkman,
            a.JOL_Lecture_Mobile,
            a.JOL_Lecture_Email,
            a.JOL_Lecture_Fax,
            a.JOL_Lecture_VerifyStatus
            --c.JOL_Employer_Email
    FROM    dbo.JOL_Lecture AS a
            INNER JOIN dbo.JOL_ApplyField AS b ON a.JOL_Lecture_Purpose = b.JOL_Field_No
            INNER JOIN dbo.JOL_Employer AS c ON b.JOL_Field_ApplyCompany = c.JOL_Employer_Account
    WHERE   b.JOL_Field_Purpose = '宣讲会'
            AND ( c.JOL_Employer_Name LIKE '%' + @JOL_Field_ApplyCompany + '%' )
            AND ( a.JOL_Lecture_Title LIKE '%' + @JOL_Lecture_Title + '%' )
    ORDER BY a.JOL_Lecture_VerifyStatus,
            a.JOL_Lecture_UpdateTime DESC,
            a.JOL_Lecture_AddTime DESC


GO
